package sf.database.dialect.mysql;

import com.querydsl.sql.MySQLTemplates;
import com.querydsl.sql.SQLTemplates;
import org.jooq.SQLDialect;
import sf.core.DBField;
import sf.database.dbinfo.Feature;
import sf.database.dialect.DBDialect;
import sf.database.dialect.DBProperty;
import sf.database.dialect.DatabaseDriver;
import sf.database.dialect.Keywords;
import sf.database.jdbc.type.TypeHandler;
import sf.database.meta.ColumnMapping;
import sf.database.meta.TableMapping;
import sf.database.support.RDBMS;
import sf.tools.JavaTypeUtils;
import sf.tools.StringUtils;

import java.sql.Connection;
import java.sql.Types;
import java.util.Arrays;
import java.util.HashSet;
import java.util.List;
import java.util.Map;

public class MySqlDialect extends DBDialect {
    @Override
    public DatabaseDriver getDatabaseDriver() {
        return DatabaseDriver.MYSQL;
    }

    @Override
    public RDBMS getName() {
        return RDBMS.mysql;
    }

    public MySqlDialect() {
        this.keywords.addAll(Keywords.MYSQL);
        // 在MYSQL中 ||是逻辑运算符
        features = new HashSet<>();
        features.addAll(Arrays.asList(Feature.DBNAME_AS_SCHEMA, Feature.SUPPORT_INLINE_COMMENT, Feature.ALTER_FOR_EACH_COLUMN, Feature.NOT_FETCH_NEXT_AUTOINCREAMENTD, Feature.SUPPORT_LIMIT, Feature.COLUMN_DEF_ALLOW_NULL));
        setProperty(DBProperty.ADD_COLUMN, "ADD");
        setProperty(DBProperty.MODIFY_COLUMN, "MODIFY");
        setProperty(DBProperty.DROP_COLUMN, "DROP COLUMN");
        setProperty(DBProperty.CHECK_SQL, "select 1");
        setProperty(DBProperty.SELECT_EXPRESSION, "select %s");
        setProperty(DBProperty.WRAP_FOR_KEYWORD, "``");
        setProperty(DBProperty.GET_IDENTITY_FUNCTION, "SELECT LAST_INSERT_ID()");
        setProperty(DBProperty.INDEX_LENGTH_LIMIT, "767");
        setProperty(DBProperty.INDEX_LENGTH_LIMIT_FIX, "255");
        setProperty(DBProperty.INDEX_LENGTH_CHARESET_FIX, "charset=latin5");
        setProperty(DBProperty.DROP_INDEX_TABLE_PATTERN, "%1$s ON %2$s");
        setProperty(DBProperty.DROP_FK_PATTERN, "alter table %1$s drop foreign key %2$s");
        setProperty(DBProperty.NVL_FUNCTION,"ifnull(%1$s,%2$s)");
    }

    public String evalFieldType(ColumnMapping mf) {
        String type = "";
        if (mf.getColumn() == null || StringUtils.isBlank(getColumnDefinition(mf))) {
            String other = "";
            if (mf.getColumn() != null && mf.getColumn().unique()) {
                other = " UNIQUE ";
            }
            if (mf.getColumn() != null && !mf.getColumn().nullable()) {
                other = other + " NOT NULL ";
            }
            type = _evalFieldType(mf) + other;
        }
        //覆盖columnDefinition
        String definition = getColumnDefinition(mf);
        if (StringUtils.isNotBlank(definition)) {
            type = definition;
        }
        //处理自定义映射
        if (mf.getType() != null) {
            Class<? extends TypeHandler> clz = mf.getType().value();
            try {
                TypeHandler handler = clz.newInstance();
                switch (handler.getSqlType()) {
                    case Types.VARCHAR:
                        type = "TEXT";
                        break;
                    default:
                        break;
                }
            } catch (InstantiationException | IllegalAccessException e) {
                throw new RuntimeException(e);
            }
        }
        // 其它的参照默认字段规则 ...
        return type;
    }

    private String _evalFieldType(ColumnMapping mf) {
        String type = "";
        int length = getColumnLength(mf);
        int scale = getColumnScale(mf);
        int precision = getColumnPrecision(mf);
        switch (mf.getSqlType()) {
            case Types.VARCHAR: {
                type = "VARCHAR(" + length + ")";
                break;
            }
            case Types.TINYINT: {
                type = "TINYINT";
                break;
            }
            case Types.SMALLINT: {
                type = "SMALLINT";
                break;
            }
            case Types.INTEGER: {
                type = "INT";
                break;
            }
            case Types.BIGINT: {
                type = "BIGINT";
                break;
            }
            case Types.FLOAT: {
                type = "FLOAT";
                break;
            }
            case Types.DOUBLE:
                type = "DOUBLE";
                break;
            case Types.BOOLEAN: {
                type = "TINYINT(1)";
                break;
            }
            case Types.DECIMAL: {
                // BigDecimal
                if (scale > 0 && precision > 0) {
                    type = "DECIMAL(" + precision + "," + scale + ")";
                } else {
                    throw new UnsupportedOperationException("Unsupport type!");
                }
                break;
            }
            case Types.NUMERIC: {
                if (precision > 0) {
                    type = "NUMERIC(" + precision + "," + scale + ")";
                } else {
                    type = "NUMERIC(255,0)";
                }
                break;
            }
            case Types.DATE:
                type = "DATE";
                break;
            case Types.TIME:
                type = "TIME";
                break;
            case Types.TIMESTAMP:
                type = "DATETIME";
                break;
            case Types.BLOB:
            case Types.BINARY:
            case Types.VARBINARY:
            case Types.LONGVARBINARY:
                if (mf.isLob() && mf.getColumn() != null) {
                    if (length > 65535 && length <= 16777215/*2的16次方减1*/) {
                        type = "MediumBlob";//最大长度 16777215 个字元(2^24-1)
                    } else if (length > 16777215/*2的24次方减1*/) {
                        type = "LongBlob";//最大长度4294967295个字元 (2^32-1)
                    }
                }
                if (StringUtils.isBlank(type)) {
                    //默认
                    type = "BLOB";//最大长度65535个字元(2^16-1)
                }
                break;
            case Types.CLOB:
            case Types.LONGVARCHAR:
            case Types.LONGNVARCHAR: {
                if (mf.isLob() && mf.getColumn() != null) {
                    if (length > 65535 && length <= 16777215/*2的16次方减1*/) {
                        type = "MediumText";//最大长度 16777215 个字元(2^24-1)
                    } else if (length > 16777215/*2的24次方减1*/) {
                        type = "LongText";//最大长度4294967295个字元 (2^32-1)
                    }
                }
                if (StringUtils.isBlank(type)) {
                    //默认
                    type = "TEXT";//最大长度65535个字元(2^16-1)
                }
                break;
            }
            default:
                type = super.evalFieldType(mf);
                break;
        }
        return type;
    }

    @Override
    public boolean createEntity(Connection conn, TableMapping en) {
        if (en.getTableName() == null) {
            throw new RuntimeException("table name is empty!");
        }
        StringBuilder sb = new StringBuilder("CREATE TABLE " + wrapKeyword(en.getTableName()) + "(");
        // 创建字段
        for (Map.Entry<DBField, ColumnMapping> mf : en.getSchemaMap().entrySet()) {
            ColumnMapping cm = mf.getValue();

            sb.append('\n').append(wrapKeyword(cm.getRawColumnName()));
            sb.append(' ').append(evalFieldType(cm));
            // 非主键的 @Name，应该加入唯一性约束

            // 普通字段
            // 下面的关于Timestamp处理，是因为MySql中第一出现Timestamp的话，如果没有设定default，数据库默认会设置为CURRENT_TIMESTAMP
            if (cm.isUnsigned()) {
                sb.append(" UNSIGNED");
            }

            if (cm.isPk()) {
                sb.append(" NOT NULL");
            }

            if (cm.getGv() != null) {
                if (JavaTypeUtils.isNumberClass(cm.getClz())) {
                    sb.append(" AUTO_INCREMENT");
                }
            }

            if (cm.getComment() != null) {
                sb.append(" COMMENT '").append(cm.getComment().value()).append("'");
            }

            sb.append(',');
        }
        // 创建主键
        List<ColumnMapping> pks = en.getPkFields();
        if (!pks.isEmpty()) {
            sb.append('\n');
            sb.append("PRIMARY KEY (");
            boolean f = false;
            for (ColumnMapping pk : pks) {
                sb.append(f ? "," : "").append(wrapKeyword(pk.getRawColumnName()));
                f = true;
            }
            sb.append(") \n ");
        }

        // 结束表字段设置
        sb.setCharAt(sb.length() - 1, ')');
        // 设置特殊引擎
//		if (en.hasMeta(META_ENGINE)) {
//			sb.append(" ENGINE=" + en.getMeta(META_ENGINE));
//		}
        // 默认采用 UTF-8 编码
//        if (en.hasMeta(META_CHARSET)) {
//            sb.append(" CHARSET=" + en.getMeta(META_CHARSET));
//        } else {
//            sb.append(" CHARSET=utf8");
//        }

        // 表名注释
        if (en.getComment() != null) {
            sb.append(" COMMENT='").append(en.getComment().value()).append("'");
        }
        // 执行创建语句
        execute(sb.toString(), conn);

        // 创建索引
        List<String> list = createIndexSql(en);
        for (String sql : list) {
            execute(sql, conn);
        }

        //创建约束
        list = createUniqueSql(en);
        for (String sql : list) {
            execute(sql, conn);
        }

        // 创建关联表
        createRelation(conn, en);
        return true;
    }

    @Override
    public String uniqueSql(String tableName, String name, String[] columnNames) {
        StringBuilder sb = new StringBuilder();
        sb.append("alter table ").append(wrapKeyword(tableName)).append(" add unique ").append(name).append("(");
        boolean f = false;
        for (int i = 0; i < columnNames.length; i++) {
            String column = columnNames[i];
            sb.append(f ? "," : "").append(wrapKeyword(column));
            f = true;
        }
        sb.append(")");
        return sb.toString();
    }

    @Override
    protected String escapeSqlValue(String value) {
        return StringUtils.replace(value, "'", "\\'");
    }

    @Override
    public String sqlTableDrop(String table) {
        return String.format("drop table if exists %s;", wrapKeyword(table));
    }

    @Override
    public String sqlTableRename(String oldName, String newName) {
        return String.format("rename table  %s to %s;", wrapKeyword(oldName), wrapKeyword(newName));
    }

    @Override
    public String sqlColumnAdd(String table, String column_definition, String column_position) {
        String sql = String.format("alter table %s add %s", wrapKeyword(table), column_definition);
        if (supportsColumnPosition() && column_position != null) {
            sql = sql + " " + column_position;
        }
        return sql + ";";
    }

    @Override
    public String sqlColumnModify(String table, String column_definition, String column_position) {
        String sql = String.format("alter table %s modify %s", wrapKeyword(table), column_definition);
        if (supportsColumnPosition() && column_position != null) {
            sql = sql + " " + column_position;
        }
        return sql + ";";
    }

    @Override
    public String sqlColumnDrop(String table, String column) {
        return String.format("alter table %s drop %s;", wrapKeyword(table), wrapKeyword(column));
    }

    @Override
    public String sqlPageList(String sql, long offset, int limit) {
        if (offset > 0) {
            return sql + " limit " + offset + "," + limit;
        } else {
            return sql + " limit " + limit;
        }
    }

    @Override
    public boolean supportsColumnPosition() {
        return true;
    }

    @Override
    public String getHibernateDialect() {
        return "org.hibernate.dialect.MySQLDialect";
    }

    @Override
    public String getCatalog(String catalog) {
        return catalog;
    }

    private static SQLTemplates sqlTemplates = MySQLTemplates.DEFAULT;

    @Override
    public SQLTemplates getQueryDslDialect() {
        return sqlTemplates;
    }

    @Override
    public SQLDialect getJooqDialect() {
        return SQLDialect.MYSQL;
    }
}
